

alter proc sp_return_view_orders_info 
@product_name varchar(30),
@customer_id varchar(30),
@sales_order_no varchar (30),
@status_from varchar(50),
@is_sample int,
@endUser varchar(50),
@year int,
@USER_ID VARCHAR(50)

as
begin
	
	declare @results Table (Status varchar (100))
	declare @status_into varchar(100), @temp_status varchar(30)
	declare @sales_id int
	DECLARE @USER_TYPE INT
	SET @USER_TYPE = 0 --NORMAL CHIPTRAC USER
	set @status_into  = ''
	
	SELECT @USER_TYPE = USER_TYPE FROM USER_TABLE WHERE USER_ID = @USER_ID
	-- This is for the status return
	if @product_name ='0' and @customer_id ='0' and @sales_order_no <>'' and @status_from <> '0'
	begin
		declare @statusCursor cursor
		IF ( @USER_TYPE = 3 ) --SALES PERSON
		 BEGIN
			DECLARE @SALESPERSON_ID INT
			SELECT @SALESPERSON_ID = SALESPERSON_ID FROM USER_MAP_TABLE WHERE USER_ID = @USER_ID
			SET @statusCursor = cursor for select distinct Status from fn_load_view_sales_orders(0,0,@is_sample,'All',@endUser) where SalesOrder_No= @sales_order_no and Status <>'PARTIALLY SHIPPED' AND CUSTOMER_ID IN (SELECT CUSTOMER_ID FROM CUSTOMER_SALESPERSON_RELATIONSHIP WHERE SALESPERSON_ID = @SALESPERSON_ID AND RELATIONSHIPSTATUS_ID = (SELECT RELATIONSHIPSTATUS_ID FROM Customer_Relationship_Status WHERE [NAME]='Active'))
		 END
		ELSE
		 BEGIN
			SET @statusCursor = cursor for select distinct Status from fn_load_view_sales_orders(0,0,@is_sample,'All',@endUser) where SalesOrder_No= @sales_order_no and Status <>'PARTIALLY SHIPPED'
		 END

		-- select Status from fn_load_view_orders(0,0,1,'All') where SalesOrder_No= 10287 and Status <>'PARTIALLY SHIPPED'
		open @statusCursor
		fetch next from @statusCursor into @temp_status
		while @@fetch_status = 0
		begin
			if @temp_status = 'APPROVED'
				set @temp_status = 'BACKLOG'
			print @status_into
			if @status_into  <>''
			begin
				set @status_into =@status_into+ ', '+@temp_status
			end
			else
			begin
				set @status_into = @temp_status
			end
		fetch next from @statusCursor into @temp_status
		end
		if @status_into <>''
			begin
				IF ( @year IS NULL OR @year = 0 )
				 BEGIN
					select @sales_id = SalesOrder_ID from sales_order where SalesOrder_No =  @sales_order_no
				 END
				ELSE
				 BEGIN
					select @sales_id = SalesOrder_ID from sales_order where SalesOrder_No =  @sales_order_no and DatePart(YYYY, SO_Booking_Date) = @year
				 END
				if @sales_id is null
				begin
					insert into @results values('No results found with the given search criteria.' )	
				end
				else
				begin
 					set @status_into = @sales_order_no+' Exists in '+ @status_into +' status'
 					insert into @results values(@status_into )
				end
			end
		else 
			insert into @results values('No results found with the given search criteria.' )
		close @statusCursor
		deallocate @statusCursor
	end
	/*
	-- this is for the customer return
	else if @product_name <>'0' and @customer_id <>'0' and @sales_order_no = '0' and @status_from <>'0'
	begin
		
		declare @temp_part_number1 varchar (50)
		declare @part_number1 varchar (500)
		set @part_number1  = ''
		set @customer_id = convert(int, @customer_id)
		declare customerCursor cursor for
		select distinct part_number from fn_load_view_orders(0,0,1,'All') where customer_id = @customer_id
		--select distinct part_number from fn_load_view_orders(0,0,1,'All') where customer_id =6
		--select *from fn_load_view_orders(0,0,1,'All') where SalesOrder_No= 10382
		open customerCursor
		fetch next from customerCursor into @temp_part_number1 
		while @@fetch_status = 0
		begin
			if @part_number1   <>''
			begin
				set @part_number1   =@part_number1 + ', '+@temp_part_number1
				print @part_number1   
			end
			else
			begin
				set @part_number1 = @temp_part_number1
			end
			fetch next from customerCursor into @temp_part_number1
		end
		print @part_number1
		if @part_number1 <>''
			insert into @results values(@part_number1)
		else
			insert into @results values('This customer does not have any part numbers listed')
		
	
		close customerCursor
		deallocate customerCursor
	end


	-- this routine occurs if the params are not null 
	else if @product_name <>'0' and @customer_id <>'0' and @sales_order_no <>'0' and @status_from<>'0'
	begin
		print 'In here all the params not null routine'
		declare @temp_part_number varchar (20)
		
		declare @temp_customer_name varchar (50), @info varchar (500)
		
		set @info  = ''
		declare salesCursor cursor for
		select distinct Part_Number, Customer_Name from fn_load_view_orders(0,0,1,'All') where SalesOrder_No=  @sales_order_no
		--select *from fn_load_view_orders(0,0,1,'All') where SalesOrder_No= 10382
		open salesCursor
		fetch next from salesCursor into @temp_part_number, @temp_customer_name
		while @@fetch_status = 0
		begin
			if @info <>''
			begin
				set @info  =@info + ', '+@temp_part_number + ' - '+@temp_customer_name
			end
			else
			begin
				set @info = @temp_part_number + ' - '+@temp_customer_name
			end
			fetch next from salesCursor into @temp_part_number, @temp_customer_name
		end
		set @info = 'For '+@sales_order_no+' Products and customers that match are '+@info
		insert into @results values(@info)
		close salesCursor
		deallocate salesCursor
	end

	-- Product name and salesorder no
	else if @product_name <>'0' and @sales_order_no <>'0' and @customer_id = '0' and @status_from<>'0'
	begin
		
		declare @temp_part_number2 varchar (20), @info1 varchar (500)
		
		
		set @info1  = ''
		declare productCursor cursor for
		select distinct Part_Number from fn_load_view_orders(0,0,1,'All') where SalesOrder_No=  @sales_order_no
		--select *from fn_load_view_orders(0,0,1,'All') where SalesOrder_No= 10382
		open productCursor
		fetch next from productCursor into @temp_part_number2
		while @@fetch_status = 0
		begin
			if @info1 <>''
			begin
				set @info1  =@info + ', '+@temp_part_number2 
			end
			else
			begin
				set @info1 = @temp_part_number2 
			end
			fetch next from productCursor into @temp_part_number2
		end
		insert into @results values(@info1)
		close productCursor
		deallocate productCursor
	end


	else if @product_name ='0' and @sales_order_no <>'0' and @customer_id <> '0' and @status_from<>'0'
	begin
		print 'in here'
		declare @temp_customer_name1 varchar (20), @info2 varchar (500),@temp_status1 varchar(20)
		
		set @info2  = ''
		declare customerCursor1 cursor for
		select distinct Customer_Name, Status from fn_load_view_orders(0,0,1,'All') where SalesOrder_No=  @sales_order_no 
		--select *from fn_load_view_orders(0,0,1,'All') where SalesOrder_No= 10382
		open customerCursor1
		fetch next from customerCursor1 into @temp_customer_name1,@temp_status1
		while @@fetch_status = 0
		begin
			if @info2 <>''
			begin
				set @info2  =@info + ', '+@temp_customer_name1 +'-'+@temp_status1
			end
			else
			begin
				set @info2 = @temp_customer_name1 +'-'+@temp_status1
			end
			fetch next from customerCursor1 into @temp_customer_name1,@temp_status1
		end
		insert into @results values(@info2)
		close customerCursor1
		deallocate customerCursor1
	end	
	*/

	select * from @results

end


